package com.qf.dao.daoimpl;

import com.qf.dao.UserDao;
import com.qf.pojo.User;
import com.qf.utils.dbutilUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * @author Mr.Liu
 * @date 2021/11/19 10:32
 */

public class UserDaoImpl implements UserDao {
    /**
     * 查找所有用户数据访问接口实现层
     * */
    @Override
    public List<User> findAllUser() throws SQLException {
        QueryRunner qr = new QueryRunner(dbutilUtils.getDataSource());
        String sql = "select * from user;";
        List<User> user = qr.query(sql, new BeanListHandler<User>(User.class));
        return user;
    }
    /**
     * 通过id查找用户数据访问接口实现层
     * */
    @Override
    public User selectById(String uid) throws SQLException {
        QueryRunner qr = new QueryRunner(dbutilUtils.getDataSource());
        String sql = "select * from user where uid = ?";
        User user1 = qr.query(sql, new BeanHandler<User>(User.class), uid);
        return user1;
    }
    /**
     * 修改用户数据访问接口实现层
     * */
    @Override
    public void modifyUser(User user) throws SQLException {
        QueryRunner qr = new QueryRunner(dbutilUtils.getDataSource());
        String sql = "update user set username = ?,password = ?,email = ?,telephone = ?,birthday = ?,sex = ? where uid = ?;";
        int num = qr.update(sql, user.getUsername(), user.getPassword(), user.getEmail(), user.getTelephone(), user.getBirthday(), user.getSex(), user.getUid());
    }
    /**
     * 删除用户数据访问接口实现层
     * */
    @Override
    public void deleteUser(String uid) throws SQLException {
        QueryRunner qr = new QueryRunner(dbutilUtils.getDataSource());
        String sql = "delete from user where uid = ?;";
        int num = qr.update(sql, uid);
    }
    /**
     * 分页查询用户数据访问接口实现层
     * */
    @Override
    public int FindCount() throws SQLException {
        QueryRunner qr = new QueryRunner(dbutilUtils.getDataSource());
        String sql = "select count(uid) from user;";
        Object obj = qr.query(sql, new ScalarHandler<>());//返回为Object类型
        int i = Integer.parseInt(String.valueOf(obj));
        return i;
    }

    @Override
    public List<User> FindUser(int currentPage, int pageSize) throws SQLException {
        QueryRunner qr = new QueryRunner(dbutilUtils.getDataSource());
        String sql = "select * from user limit ?,?;";
        List<User> users = qr.query(sql, new BeanListHandler<User>(User.class), ((currentPage-1)*pageSize), pageSize);
        return users;
    }
    /*
    * 校验用户
    * */
    @Override
    public User checkUser(String username) throws SQLException {
        QueryRunner qr = new QueryRunner(dbutilUtils.getDataSource());
        String sql = "select * from user where username = ?;";
        User user = qr.query(sql, new BeanHandler<User>(User.class), username);
        return user;
    }
    /*
    * 注册用户
    * */
    @Override
    public void registerUser(User user) throws SQLException {
        QueryRunner qr = new QueryRunner(dbutilUtils.getDataSource());
        String sql = "insert into user values(?,?,?,?,?,?,?,?,?,?);";
        int i = qr.update(sql, user.getUid(), user.getUsername(), user.getPassword(), user.getName(), user.getEmail(), user.getTelephone(), user.getBirthday(), user.getSex(), user.getState(), user.getCode());
        System.out.println(i+"行受影响");
    }

    @Override
    public User findUserByCode(String code) throws SQLException {
        QueryRunner qr = new QueryRunner(dbutilUtils.getDataSource());
        String sql = "select * from user where code = ?;";
        User user = qr.query(sql, new BeanHandler<User>(User.class), code);
        return user;
    }
    /*
    * 更新用户状态
    * */
    @Override
    public void updateUser(User user) throws SQLException {
        QueryRunner qr = new QueryRunner(dbutilUtils.getDataSource());
        String sql = "update user set state = ? where uid = ?";
        int i = qr.update(sql, user.getState(), user.getUid());
    }

    /**
     *通过用户名查找用户
     * @param username
     * @return
     * @throws SQLException
     */
    @Override
    public User selectUser(String username,String password) throws SQLException {
        QueryRunner qr = new QueryRunner(dbutilUtils.getDataSource());
        String sql = "select * from user where username = ? and password = ?;";
        User user = qr.query(sql, new BeanHandler<User>(User.class), username,password);
        return user;
    }
}
